package com.hwg.emp.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hwg.emp.common.dto.KeyQueryDto;
import com.hwg.emp.common.dto.KeysQueryDto;
import com.hwg.emp.common.util.DBUtil;
import com.hwg.emp.dao.AttendanceDAO;
import com.hwg.emp.dto.AttendanceDto;
import com.hwg.emp.dto.PageBean;
import com.hwg.emp.model.AttendanceMD;
import com.hwg.emp.model.EmployeeMD;

public class AttendanceDAOImpl implements AttendanceDAO{

	@Override
	public int insert(AttendanceMD t) throws Exception {
		return 0;
		
	}

	@Override
	public int delete(String uk, String ukValue) throws Exception {
		return 0;
		
	}

	@Override
	public int update(AttendanceMD t) throws Exception {
		return 0;
		
	}
	@Override
	public int selectEmpAttendance(String deptId) throws Exception {
		// TODO Auto-generated method stub
		int account=0;
		StringBuilder sql=new StringBuilder();
		sql.append("SELECT COUNT( * ) scount ")
		.append("FROM rcd_attendance ra,sys_user su,emp_employee ee ")
		.append("WHERE ra.emp_id=ee.id and ee.id=su.id and ee.dept_id=?");
		Connection con=DBUtil.getConnection();
		PreparedStatement pst=con.prepareStatement(sql.toString());
		pst.setString(1, deptId);
		ResultSet rs=pst.executeQuery();
		if(rs.next()){
			account=rs.getInt("scount");
		}
		return account;
	}
	@Override
	public List<AttendanceDto> selectEmpAttendance(String deptId,PageBean pageBean) throws Exception {
		// TODO Auto-generated method stub
		List<AttendanceDto> attendanceDtos=new ArrayList<AttendanceDto>();
		Connection con=DBUtil.getConnection();
		StringBuilder sql=new StringBuilder();
		sql.append("SELECT su.`name`,ra.starttime,ra.endtime,ra.absentdays,ra.askoffdays,")
		.append("ra.checkdays,ra.earlytimes,ra.latetimes,ra.overtimedays ")
		.append("FROM rcd_attendance ra,sys_user su,emp_employee ee ")
		.append("WHERE ra.emp_id=ee.id and ee.id=su.id and ee.dept_id=? limit ?,?");
		PreparedStatement pst=con.prepareStatement(sql.toString());
		pst.setString(1, deptId);
		pst.setInt(2,pageBean.getStart());
		pst.setInt(3, pageBean.getPrecord());
		ResultSet rs=pst.executeQuery();
		while(rs.next()){
			AttendanceDto attendanceDto=new AttendanceDto();
			attendanceDto.setName(rs.getString("name"));
			attendanceDto.setStarttime(rs.getTimestamp("starttime"));
			attendanceDto.setEndtime(rs.getTimestamp("endtime"));
			attendanceDto.setAbsentdays(rs.getInt("absentdays"));
			attendanceDto.setAskoffdays(rs.getInt("askoffdays"));
			attendanceDto.setCheckdays(rs.getInt("checkdays"));
			attendanceDto.setOvertimedays(rs.getInt("overtimedays"));
			attendanceDto.setEarlytimes(rs.getInt("earlytimes"));
			attendanceDto.setLatetimes(rs.getInt("latetimes"));
			attendanceDtos.add(attendanceDto);
		}
		
		return attendanceDtos;
	}

	@Override
	public AttendanceMD select(String uk, String ukValue) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean exist(String uk, String ukValue) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}
	@Override
	public List<AttendanceDto> selectEmpAttendance(PageBean pageBean) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int selectAttendanceByKey(KeysQueryDto dto, List<AttendanceMD> pageData) throws Exception {
		// TODO Auto-generated method stub
		//查询总记录数
				int totalCount=0;
				Connection con =DBUtil.getConnection();
				StringBuffer whereSql=new StringBuffer();
				whereSql.append(" from rcd_attendance where emp_id=?");
				if (dto.getKeys().get("askoffdays")!=null) {
					whereSql.append(" or askoffdays =?");
				}
				if (dto.getKeys().get("startdate")!=null) {
					whereSql.append(" or startdate=?");
				}
				if (dto.getKeys().get("enddate")!=null) {
					whereSql.append(" or enddate=?");
				}
				if (dto.getKeys().get("overtimedays")!=null) {
					whereSql.append(" or overtimedays =?");
				}
				if (dto.getKeys().get("latetimes")!=null) {
					whereSql.append(" or latetimes =?");
				}
				if (dto.getKeys().get("absentdays")!=null) {
					whereSql.append(" or absentdays =?");
				}
				if (dto.getKeys().get("earlytimes")!=null) {
					whereSql.append(" or earlytimes =?");
				}
				if (dto.getKeys().get("checkdays")!=null) {
					whereSql.append(" or checkdays =?");
				}
				
				PreparedStatement pst=con.prepareStatement("select count(*)"+whereSql.toString());
				int i=0;
				pst.setString(++i, (String)dto.getKeys().get("id"));
				if (dto.getKeys().get("askoffdays")!=null) {
					pst.setDouble(++i, (Integer)(dto.getKeys().get("askoffdays")));
				}
				if (dto.getKeys().get("startdate")!=null) {
					pst.setDate(++i, (Date)(dto.getKeys().get("startdate")));
				}
				if (dto.getKeys().get("enddate")!=null) {
					pst.setDate(++i, (Date)(dto.getKeys().get("enddate")));
				}
				if (dto.getKeys().get("overtimedays")!=null) {
					pst.setDouble(++i, (Integer)(dto.getKeys().get("overtimedays")));
				}
				if (dto.getKeys().get("latetimes")!=null) {
					pst.setDouble(++i, (Integer)(dto.getKeys().get("latetimes")));
				}
				if (dto.getKeys().get("absentdays")!=null) {
					pst.setDouble(++i, (Integer)(dto.getKeys().get("absentdays")));
				}
				if (dto.getKeys().get("earlytimes")!=null) {
					pst.setDouble(++i, (Integer)(dto.getKeys().get("earlytimes")));
				}
				if (dto.getKeys().get("checkdays")!=null) {
					pst.setDouble(++i, (Integer)(dto.getKeys().get("checkdays")));
				}
				ResultSet rs=pst.executeQuery();
				if (rs.next()) {
					totalCount=rs.getInt(1);
				}
				rs.close();
				pst.close();
				
				//分页查询
						StringBuilder selectSql=new StringBuilder();
						selectSql.append("select * ").append(whereSql);
						selectSql.append(" limit ").append((dto.getPageNo()-1)*dto.getPageSize())
						.append(",").append(dto.getPageSize());
						pst=con.prepareStatement(selectSql.toString());
						i=0;
						pst.setString(++i, (String)dto.getKeys().get("id"));
						if (dto.getKeys().get("askoffdays")!=null) {
							pst.setDouble(++i, (Integer)(dto.getKeys().get("askoffdays")));
						}
						if (dto.getKeys().get("startdate")!=null) {
							pst.setDate(++i, (Date)(dto.getKeys().get("startdate")));
						}
						if (dto.getKeys().get("enddate")!=null) {
							pst.setDate(++i, (Date)(dto.getKeys().get("enddate")));
						}
						if (dto.getKeys().get("overtimedays")!=null) {
							pst.setDouble(++i, (Integer)(dto.getKeys().get("overtimedays")));
						}
						if (dto.getKeys().get("latetimes")!=null) {
							pst.setDouble(++i, (Integer)(dto.getKeys().get("latetimes")));
						}
						if (dto.getKeys().get("absentdays")!=null) {
							pst.setDouble(++i, (Integer)(dto.getKeys().get("absentdays")));
						}
						if (dto.getKeys().get("earlytimes")!=null) {
							pst.setDouble(++i, (Integer)(dto.getKeys().get("earlytimes")));
						}
						if (dto.getKeys().get("checkdays")!=null) {
							pst.setDouble(++i, (Integer)(dto.getKeys().get("checkdays")));
						}
						rs=pst.executeQuery();
						while (rs.next()) {
							AttendanceMD at=new AttendanceMD();
							EmployeeMD emp=new EmployeeMD();
							emp.setId(rs.getString("emp_id"));
							at.setId(rs.getString("id"));
							at.setStarttime(rs.getDate("starttime"));
							at.setEndtime(rs.getDate("endtime"));
							at.setAskoffdays(rs.getInt("askoffdays"));
							at.setOvertimedays(rs.getInt("overtimedays"));
							at.setLatetimes(rs.getInt("latetimes"));
							at.setAbsentdays(rs.getInt("absentdays"));
							at.setEarlytimes(rs.getInt("earlytimes"));
							at.setCheckdays(rs.getInt("checkdays"));
							pageData.add(at);
						}
				
				return totalCount;
	}

}
